USE [master]
GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'HooIzDat')
	CREATE DATABASE HooIzDat;	
GO

USE [HooIzDat]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/* RUN C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe to setup the ASP.NET provider DB objects */

/****** Object:  Table [dbo].[Character]    Script Date: 09/03/2009 20:52:25 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Character]') AND type in (N'U'))
	
	CREATE TABLE [dbo].[Character](
		[CharacterID] [int] IDENTITY(1,1) NOT NULL,
		[Name] [nvarchar](100) NOT NULL,
	 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
	(
		[CharacterID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Trait]    Script Date: 09/03/2009 20:53:07 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trait]') AND type in (N'U'))
BEGIN
	
	CREATE TABLE [dbo].[Trait](
		[TraitID] [int] IDENTITY(1,1) NOT NULL,
		[ParentTraitID] [int] NULL,
		[Name] [nvarchar](100) NOT NULL,
		[QuestionFormat] [nvarchar](1000) NOT NULL,
	 CONSTRAINT [PK_Trait] PRIMARY KEY CLUSTERED 
	(
		[TraitID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	
	ALTER TABLE [dbo].[Trait]  WITH CHECK ADD  CONSTRAINT [FK_Trait_Parent] FOREIGN KEY([ParentTraitID])
	REFERENCES [dbo].[Trait] ([TraitID])

	ALTER TABLE [dbo].[Trait] CHECK CONSTRAINT [FK_Trait_Parent]
END

/****** Object:  Table [dbo].[TraitVariation]    Script Date: 09/03/2009 20:54:26 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TraitVariation]') AND type in (N'U'))
BEGIN
	
	CREATE TABLE [dbo].[TraitVariation](
		[TraitVariationID] [int] IDENTITY(1,1) NOT NULL,
		[TraitID] [int] NOT NULL,
		[Name] [nvarchar](100) NOT NULL,
	 CONSTRAINT [PK_TraitVariation] PRIMARY KEY CLUSTERED 
	(
		[TraitVariationID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[TraitVariation]  WITH CHECK ADD  CONSTRAINT [FK_TraitVariation_Trait] FOREIGN KEY([TraitID])
	REFERENCES [dbo].[Trait] ([TraitID])


	ALTER TABLE [dbo].[TraitVariation] CHECK CONSTRAINT [FK_TraitVariation_Trait]

END

/****** Object:  Table [dbo].[CharacterTrait]    Script Date: 09/03/2009 20:53:55 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CharacterTrait]') AND type in (N'U'))
BEGIN

	CREATE TABLE [dbo].[CharacterTrait](
		[CharacterID] [int] NOT NULL,
		[TraitID] [int] NOT NULL,
		[TraitVariationID] [int] NULL,
	 CONSTRAINT [PK_PersonTrait] PRIMARY KEY CLUSTERED 
	(
		[CharacterID] ASC,
		[TraitID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[CharacterTrait]  WITH CHECK ADD  CONSTRAINT [FK_PersonTrait_Character] FOREIGN KEY([CharacterID])
	REFERENCES [dbo].[Character] ([CharacterID])
	
	ALTER TABLE [dbo].[CharacterTrait] CHECK CONSTRAINT [FK_PersonTrait_Character]
	
	ALTER TABLE [dbo].[CharacterTrait]  WITH CHECK ADD  CONSTRAINT [FK_PersonTrait_Trait] FOREIGN KEY([TraitID])
	REFERENCES [dbo].[Trait] ([TraitID])
	
	ALTER TABLE [dbo].[CharacterTrait] CHECK CONSTRAINT [FK_PersonTrait_Trait]
	
	ALTER TABLE [dbo].[CharacterTrait]  WITH CHECK ADD  CONSTRAINT [FK_PersonTrait_TraitVariation] FOREIGN KEY([TraitVariationID])
	REFERENCES [dbo].[TraitVariation] ([TraitVariationID])
	
	ALTER TABLE [dbo].[CharacterTrait] CHECK CONSTRAINT [FK_PersonTrait_TraitVariation]
	
END

/****** Object:  Table [dbo].[Player]    Script Date: 09/03/2009 21:24:15 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Player]') AND type in (N'U'))

	CREATE TABLE [dbo].[Player](
		[PlayerID] [int] IDENTITY(1,1) NOT NULL,
		[UserName] [nvarchar](256) NOT NULL
	 CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED 
	(
		[PlayerID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

GO

/****** Object:  Index [IX_Player_UserName]    Script Date: 09/03/2009 21:25:10 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Player]') AND name = N'IX_Player_UserName')
	CREATE UNIQUE NONCLUSTERED INDEX [IX_Player_UserName] ON [dbo].[Player] 
	(
		[UserName] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Game]    Script Date: 09/03/2009 20:55:07 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Game]') AND type in (N'U'))
BEGIN

	CREATE TABLE [dbo].[Game](
		[GameID] [int] IDENTITY(1,1) NOT NULL,
		[StartedOn] [datetime] NULL,
		[FinishedOn] [datetime] NULL,
		[Player1ID] [int] NOT NULL,
		[Player2ID] [int] NULL,
		[Player1CharacterID] [int] NULL,
		[Player2CharacterID] [int] NULL,
		[WinnerID] [int] NULL,
		[QuestionsWonIn] [int] NULL,
		[CreatedOn] [datetime] NOT NULL,
	 CONSTRAINT [PK_Game] PRIMARY KEY CLUSTERED 
	(
		[GameID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[Game]  WITH CHECK ADD  CONSTRAINT [FK_Game_Player1] FOREIGN KEY([Player1ID])
	REFERENCES [dbo].[Player] ([PlayerID])

	ALTER TABLE [dbo].[Game] CHECK CONSTRAINT [FK_Game_Player1]

	ALTER TABLE [dbo].[Game]  WITH CHECK ADD  CONSTRAINT [FK_Game_Player1Character] FOREIGN KEY([Player1CharacterID])
	REFERENCES [dbo].[Character] ([CharacterID])

	ALTER TABLE [dbo].[Game] CHECK CONSTRAINT [FK_Game_Player1Character]

	ALTER TABLE [dbo].[Game]  WITH CHECK ADD  CONSTRAINT [FK_Game_Player2] FOREIGN KEY([Player2ID])
	REFERENCES [dbo].[Player] ([PlayerID])

	ALTER TABLE [dbo].[Game] CHECK CONSTRAINT [FK_Game_Player2]

	ALTER TABLE [dbo].[Game]  WITH CHECK ADD  CONSTRAINT [FK_Game_Player2Character] FOREIGN KEY([Player2CharacterID])
	REFERENCES [dbo].[Character] ([CharacterID])
	
	ALTER TABLE [dbo].[Game] CHECK CONSTRAINT [FK_Game_Player2Character]
	
	ALTER TABLE [dbo].[Game]  WITH CHECK ADD  CONSTRAINT [FK_Game_Winner] FOREIGN KEY([WinnerID])
	REFERENCES [dbo].[Player] ([PlayerID])
	
	ALTER TABLE [dbo].[Game] CHECK CONSTRAINT [FK_Game_Winner]
	
END

/****** Object:  Table [dbo].[GameState]    Script Date: 09/03/2009 20:55:28 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GameState]') AND type in (N'U'))
BEGIN

	CREATE TABLE [dbo].[GameState](
		[GameID] [int] NOT NULL,
		[ActivePlayerID] [int] NOT NULL,
		[QuestionAsked] [nvarchar](1000) NULL,
		[Answer] [bit] NULL,
		[TurnsPlayed] [int] NOT NULL,
		[GuessedCharacterID] [int] NULL,
	 CONSTRAINT [PK_GameState] PRIMARY KEY CLUSTERED 
	(
		[GameID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[GameState]  WITH CHECK ADD  CONSTRAINT [FK_GameState_Character] FOREIGN KEY([GuessedCharacterID])
	REFERENCES [dbo].[Character] ([CharacterID])
	
	ALTER TABLE [dbo].[GameState] CHECK CONSTRAINT [FK_GameState_Character]
	
	ALTER TABLE [dbo].[GameState]  WITH CHECK ADD  CONSTRAINT [FK_GameState_GameState] FOREIGN KEY([GameID])
	REFERENCES [dbo].[Game] ([GameID])
	
	ALTER TABLE [dbo].[GameState] CHECK CONSTRAINT [FK_GameState_GameState]
	
	ALTER TABLE [dbo].[GameState]  WITH CHECK ADD  CONSTRAINT [FK_GameState_Player] FOREIGN KEY([ActivePlayerID])
	REFERENCES [dbo].[Player] ([PlayerID])
	
	ALTER TABLE [dbo].[GameState] CHECK CONSTRAINT [FK_GameState_Player]
	
	ALTER TABLE [dbo].[GameState] ADD  CONSTRAINT [DF_GameState_Turns]  DEFAULT ((0)) FOR [TurnsPlayed]
	
END

/****** Object:  Table [dbo].[GameTiles]    Script Date: 09/03/2009 20:55:50 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GameTiles]') AND type in (N'U'))
BEGIN

	CREATE TABLE [dbo].[GameTiles](
		[GameID] [int] NOT NULL,
		[PlayerID] [int] NOT NULL,
		[CharacterID] [int] NOT NULL,
		[IsOpen] [bit] NOT NULL,
		[TileIndex] [int] NOT NULL,
	 CONSTRAINT [PK_GameTiles_1] PRIMARY KEY NONCLUSTERED 
	(
		[GameID] ASC,
		[PlayerID] ASC,
		[CharacterID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[GameTiles]  WITH CHECK ADD  CONSTRAINT [FK_GameTiles_Character] FOREIGN KEY([CharacterID])
	REFERENCES [dbo].[Character] ([CharacterID])

	ALTER TABLE [dbo].[GameTiles] CHECK CONSTRAINT [FK_GameTiles_Character]

	ALTER TABLE [dbo].[GameTiles]  WITH CHECK ADD  CONSTRAINT [FK_GameTiles_Game] FOREIGN KEY([GameID])
	REFERENCES [dbo].[Game] ([GameID])

	ALTER TABLE [dbo].[GameTiles] CHECK CONSTRAINT [FK_GameTiles_Game]
	
	ALTER TABLE [dbo].[GameTiles]  WITH CHECK ADD  CONSTRAINT [FK_GameTiles_Player] FOREIGN KEY([PlayerID])
	REFERENCES [dbo].[Player] ([PlayerID])

	ALTER TABLE [dbo].[GameTiles] CHECK CONSTRAINT [FK_GameTiles_Player]

	ALTER TABLE [dbo].[GameTiles] ADD  CONSTRAINT [DF_GameTiles_IsOpen]  DEFAULT ((1)) FOR [IsOpen]

END

/****** Object:  StoredProcedure [dbo].[DeleteAllGames]    Script Date: 09/03/2009 22:26:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteAllGames]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[DeleteAllGames]
GO

CREATE PROCEDURE [dbo].[DeleteAllGames]
AS
	DELETE FROM GameTiles;
	DELETE FROM GameState;
	DELETE FROM Game;
	RETURN

GO

/****** Object:  Login [NT AUTHORITY\NETWORK SERVICE]    Script Date: 09/03/2009 22:10:21 ******/
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'NT AUTHORITY\NETWORK SERVICE')
	CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

/****** Object:  User [NT AUTHORITY\NETWORK SERVICE]    Script Date: 09/03/2009 22:11:06 ******/
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'NT AUTHORITY\NETWORK SERVICE')
	CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember N'db_datareader', N'NT AUTHORITY\NETWORK SERVICE';
EXEC sp_addrolemember N'db_datawriter', N'NT AUTHORITY\NETWORK SERVICE';